/**
 * @name         RepNET Common methods 
 * @version      1.0a
 * @author       Kasun Rathnayaka <kazar55@gmail.com>
 * @author       Ramindu Deshapriya <rasade88@gmail.com>
 * @author 		 Thusira Dissanayake <thusira14@gmail.com>
 * @about        Developed by MiDi
 * @license	 http://www.gnu.org/licenses/lgpl-2.1.html GNU Lesser General Public License (LGPL)
 * @lastModified 2012.09.24
 */
package com.midi.wholesale.main;

import java.awt.event.ItemListener;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import com.midi.wholesale.main.Main;
import com.midi.wholesale.models.*;
import com.midi.wholesale.ui.*;
import com.mysql.jdbc.PreparedStatement;

public class Common {
	static ResultSet itemListResult;
	public static ArrayList<Lorry> lorries = null;
	
	/**
	 * Method to return list of lorry names to populate combo box
	 * @return <strong>ArrayList</strong> List of lorry names from the database
	 */
	public static ArrayList<Lorry> loadlorries() {
		ArrayList<Lorry> lorries = new ArrayList<Lorry>();
		try {
			Statement statement = Main.dbConnection.createStatement();
			ResultSet res = statement.executeQuery("SELECT * FROM lorries");
			while ( res.next() ) {
				lorries.add(new Lorry(res.getInt("id"),res.getString("name")));
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		return lorries;
	}
	
public static ArrayList<Agent> agents = null;
	
	/**
	 * Method to return list of agent names to populate combo box
	 * @return <strong>ArrayList</strong> List of agent names from the database
	 */
	public static ArrayList<Agent> loadagents() {
		ArrayList<Agent> agents = new ArrayList<Agent>();
		try {
			Statement statement = Main.dbConnection.createStatement();
			ResultSet res = statement.executeQuery("SELECT * FROM agents");
			while ( res.next() ) {
				agents.add(new Agent(res.getInt("id"),res.getString("name")));
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		return agents;
	}
	
public static ArrayList<Route> routes = null;
	
	/**
	 * Method to return list of route names to populate combo box
	 * @return <strong>ArrayList</strong> List of route names from the database
	 */
	public static ArrayList<Route> loadroutes() {
		ArrayList<Route> routes = new ArrayList<Route>();
		try {
			Statement statement = Main.dbConnection.createStatement();
			ResultSet res = statement.executeQuery("SELECT * FROM routs");
			while ( res.next() ) {
				routes.add(new Route(res.getInt("id"),res.getString("name")));
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		return routes;
	}
	
	/**
	 * Method to return list of item names to populate combo box
	 * @return <strong>ArrayList</strong> List of item names from the database
	 */
	public static ArrayList<Item> loadItems() {
		ArrayList<Item> items = new ArrayList<Item>();
		try {
			Statement statement = Main.dbConnection.createStatement();
			ResultSet res = statement.executeQuery("SELECT * FROM item");
			while ( res.next() ) {
				items.add(new Item(res.getInt("id"),res.getString("name")));
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		return items;
	}
	/**
	 * Method to return an ArrayList of delivery item results
	 * @param selectedItem
	 * @param selectedItem2
	 * @param selectedItem3
	 * @return
	 */
	public static ArrayList<HashMap<String, String>> loadDelivery(
			Lorry lorry, Agent agent, Route route) {
		ArrayList<HashMap<String,String>> results = new ArrayList<HashMap<String,String>>();
		try {
			PreparedStatement statement1 = (PreparedStatement)Main.dbConnection.prepareStatement("SELECT i.name, d.amount FROM deliveryinfo as d " +
					"LEFT JOIN items as i ON i.id=d.item_id WHERE d.lorry_id=? AND d.route_id=?");
			statement1.setString(1, String.valueOf(lorry.getId()));
			statement1.setString(2, String.valueOf(route.getId()));
			itemListResult = statement1.executeQuery();
			while ( itemListResult.next() ) {
				HashMap<String,String> line = new HashMap<String,String>();
				line.put("item_name", itemListResult.getString("name"));
				line.put("amount", itemListResult.getString("amount"));
				results.add(line);
			}
			
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		return results;
	}
	
	
	
}
